package poi;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <pre>
 * Created with IntelliJ IDEA.
 * User: zhengzhi
 * Date: 2017/7/17
 * To change this template use File | Settings | File Templates.
 * </pre>
 */
public class DataParse {


    private static String [] times = new String[]{"0:00", "0:15", "0:30", "0:45", "1:00", "1:15", "1:30", "1:45", "2:00", "2:15", "2:30",
            "2:45", "3:00", "3:15", "3:30", "3:45", "4:00", "4:15", "4:30", "4:45", "5:00", "5:15", "5:30", "5:45", "6:00", "6:15",
            "6:30", "6:45", "7:00", "7:15", "7:30", "7:45", "8:00", "8:15", "8:30", "8:45", "9:00", "9:15", "9:30", "9:45", "10:00",
            "10:15", "10:30", "10:45", "11:00", "11:15", "11:30", "11:45", "12:00", "12:15", "12:30", "12:45", "13:00", "13:15",
            "13:30", "13:45", "14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30",
            "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00", "19:15", "19:30", "19:45",
            "20:00", "20:15", "20:30", "20:45", "21:00", "21:15", "21:30", "21:45", "22:00", "22:15", "22:30", "22:45", "23:00", "23:15", "23:30", "23:45"};

    public static void main(String[] args) {
        FileInputStream inputStream = null;


        try{
            inputStream = new FileInputStream("D:/横琴泛旅游结果表-20170710.xlsx");
            Workbook workbook = WorkbookFactory.create(inputStream);

            int sheets = workbook.getNumberOfSheets();

            if(sheets > 0){
                for(int i = 0; i < 1; i++){
                    Sheet sheet = workbook.getSheetAt(i);
                    int rows = sheet.getPhysicalNumberOfRows();

                    if(rows > 1){
//                        Row row1 = sheet.getRow(2);
//                        Iterator<Cell> iterator = row1.cellIterator();
//                        ArrayList<String> titles = new ArrayList<String>();
//
//
//                        //将第一行标题行取出来
//                        while(iterator.hasNext()){
//                            Cell nextCell = iterator.next();
//
//                            CellType cellTypeEnum = nextCell.getCellTypeEnum();
//                            switch (cellTypeEnum){
//                                case NUMERIC:
//                                    System.out.println(nextCell.getNumericCellValue());
//                                    break;
//                                case STRING:
//                                    System.out.println(nextCell.getStringCellValue());
//                                    break;
//                                case FORMULA:
//                                    System.out.println(nextCell.getCellFormula());
//                                    break;
//                                default :
//                                    break;
//
//                            }
//
//
//                            if(nextCell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("HH:mm:ss")){
//                                SimpleDateFormat format = new SimpleDateFormat("HH:mm:ss");
//                                Date value = nextCell.getDateCellValue();
//                                titles.add(format.format(value));
//                            }else{
//                                titles.add(nextCell.getStringCellValue());
//                            }
//                        }

//                        //将第一列中的日期取出来
//                        HashSet<String> dateSet = new HashSet<String>();
//                        for(int a = 2; a<rows; a++){
//                            Cell cell = sheet.getRow(a).getCell(0);
//                            CellType cellTypeEnum = cell.getCellTypeEnum();
//                            switch(cellTypeEnum) {
//                                case STRING:
//                                    System.out.println(cell.getStringCellValue());
//                                    dateSet.add(cell.getStringCellValue());
//                                    break;
//                                case NUMERIC:
//                                    double d = cell.getNumericCellValue();
//                                    String dateStr =new BigDecimal(d).toString();
//                                    System.out.println(dateStr);
//                                    dateSet.add(dateStr);
//                                    break;
//                                default:
//                                    break;
//                            }
//                        }
//
//                        Iterator<String> dateIterator = dateSet.iterator();


                        ArrayList<HashMap> oneDayVisitorsCountList = new ArrayList<HashMap>();
                        String initDate = new BigDecimal(sheet.getRow(2).getCell(0).getNumericCellValue()).toString();
                        //保存某一天的所有景区下的访问量
                        HashMap<String, List> stringListHashMap = new HashMap<String, List>();

                        //遍历每一行
                        for(int j = 2; j < rows; j++) {
                            //数据从第三行开始，
                            Row row = sheet.getRow(j);
                            int cells = row.getLastCellNum();
                            String CurrDate = new BigDecimal(row.getCell(0).getNumericCellValue()).toString();

                            if (CurrDate.equals(initDate)) {
                                String sceneName = row.getCell(1).getStringCellValue();
                                ArrayList<JSONObject> strings = new ArrayList<JSONObject>();
                                if (cells > 0) {
                                    for (int k = 2; k < cells; k++) {

                                        Map<String, String> map = new HashMap<String, String>();
                                        map.put("key", times[k - 2]);
                                        map.put("value", new BigDecimal(row.getCell(k).getNumericCellValue()).toString());

                                        String jsonString = JSON.toJSONString(map);
                                        JSONObject jsonObject = JSON.parseObject(jsonString);
                                        strings.add(jsonObject);
                                    }
                                }
                                stringListHashMap.put(sceneName,strings);


                                if(j == rows-1){
                                    String jsonString = JSON.toJSONString(stringListHashMap);
                                    JSONObject jsonObject = JSON.parseObject(jsonString);
                                    HashMap<String, JSONObject> hashMap = new HashMap<String, JSONObject>();
                                    hashMap.put(initDate,jsonObject);
                                    oneDayVisitorsCountList.add(hashMap);
                                    break;
                                }

                            } else{
                                String jsonString = JSON.toJSONString(stringListHashMap);
                                JSONObject jsonObject = JSON.parseObject(jsonString);
                                HashMap<String, JSONObject> hashMap = new HashMap<String, JSONObject>();
                                hashMap.put(initDate,jsonObject);
                                oneDayVisitorsCountList.add(hashMap);

                                initDate = CurrDate;
                                stringListHashMap = new HashMap<String, List>();
                                j--;
                                continue;
                            }

                        }

                        System.out.println(JSON.toJSONString(oneDayVisitorsCountList));
                    }
                }
            }
        }catch(Exception e){
        }
    }
}






